const fs = require("fs")
const Router = require('koa-router');
const router = new Router();

var mysql = require('mysql');
const { match } = require('assert');
var db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'shops'
});
db.connect();
//用户表接口

//用户数据全读
router.post('/user_read', async ctx => {
    var sql_str = `SELECT * FROM users`
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })

    })
    ctx.body = await promise
})
//读取单个用户
router.post('/user_login', async ctx => {
    var user_name = ctx.request.body.user
    var id = ctx.request.body.id
    if (id == undefined) {
        var sql_str = `SELECT * FROM users WHERE user_name='` + user_name + `'`
    } else {
        var sql_str = `SELECT * FROM users WHERE id=` + id
    }
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })

    })
    ctx.body = await promise
})
//添加单个用户
router.post('/user_register', async ctx => {
    var user_data = ctx.request.body.data
    var sql_str = ` 
    INSERT users(name,user_name,password,PaymentPassword,Email,phone,gender,birthday,img) 
    VALUES(
        '` + user_data.name + `',
        '` + user_data.user_name + `',
        '` + user_data.password + `',
        '` + user_data.PaymentPassword + `',
        '` + user_data.Email + `',
        '` + user_data.phone + `',
        '` + user_data.gender + `',
        '` + user_data.birthday + `',
        '` + user_data.img + `')`
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })

    })
    ctx.body = await promise
})
//修改单个用户
router.post('/user_change', async ctx => {
    var user_data = ctx.request.body.data
    if (ctx.request.files != undefined) {
        var img = ctx.request.files.file
        var du = fs.createReadStream(img.path)
        var my_time = new Date()
        var my_time = my_time.getTime()
        var xie = fs.createWriteStream("./static/img/" + my_time + ".jpg")
        du.pipe(xie)
        user_data = JSON.parse(user_data)
        if (user_data.img != '')
            fs.unlink("./static/img/" + user_data.img, function (err) {
                if (err) {
                    console.log(err)
                }
            })
        user_data.img = my_time + ".jpg"
    }
    var sql_str = `
    UPDATE users 
    SET NAME='`+ user_data.name + `',
    user_name='`+ user_data.user_name + `',
    password='`+ user_data.password + `',
    PaymentPassword='`+ user_data.PaymentPassword + `',
    Email='`+ user_data.Email + `',
    phone='`+ user_data.phone + `',
    gender='`+ user_data.gender + `',
    birthday='`+ user_data.birthday + `',
    img='`+ user_data.img + `' 
    WHERE id='`+ user_data.id + `'`
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data.tou_img = my_time
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data: data })
            }
        })
    })
    ctx.body = await promise
})
//删除单个用户
router.post('/user_delete', async ctx => {
    var id = ctx.request.body.id
    var sql_str = `DELETE FROM users WHERE id=` + id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})

//地址表接口

//添加/修改 地址
router.post('/address_keep', async ctx => {
    var address_data = ctx.request.body.data
    var sql_str = `SELECT * FROM address WHERE id='` + address_data.id + `'`
    var sql_str1 = `
        INSERT INTO address(NAME,tel,province,city,county,addressDetail,areaCode,postalCode,isDefault,user_id) 
        VALUES ('`+ address_data.name + `',
        '`+ address_data.tel + `',
        '`+ address_data.province + `',
        '`+ address_data.city + `',
        '`+ address_data.county + `',
        '`+ address_data.addressDetail + `',
        '`+ address_data.areaCode + `',
        '`+ address_data.postalCode + `',
        '`+ address_data.isDefault + `',
        '`+ address_data.user_id + `')`
    var sql_str2 = `
        UPDATE address SET 
        name='`+ address_data.name + `',
        tel='`+ address_data.tel + `',
        province='`+ address_data.province + `',
        city='`+ address_data.city + `',
        county='`+ address_data.county + `',
        addressDetail='`+ address_data.addressDetail + `',
        areaCode='`+ address_data.areaCode + `',
        postalCode='`+ address_data.postalCode + `',
        isDefault='`+ address_data.isDefault + `',
        user_id='`+ address_data.user_id + `' 
        WHERE id=`+ address_data.id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '失败', err })
            } else {
                data = JSON.stringify(data)
                if (data == '[]') {
                    db.query(sql_str1, function (err1, data1, zi_duan1) {
                        if (err1) {
                            f2({ code: 1, msg: '失败', err: err1 })
                        } else {
                            data1 = JSON.stringify(data1)
                            f1({ code: 0, msg: '成功', data1 })
                        }
                    })
                } else {
                    db.query(sql_str2, function (err2, data2, zi_duan1) {
                        if (err2) {
                            f2({ code: 1, msg: '失败', err: err2 })
                        } else {
                            data2 = JSON.stringify(data2)
                            f1({ code: 0, msg: '成功', data2 })
                        }
                    })
                }
            }
        })

    })
    ctx.body = await promise
})
//读取地址
router.post('/address_read', async ctx => {
    var id = ctx.request.body.id
    var user_id = ctx.request.body.user_id
    if (id == undefined) {
        var sql_str = `SELECT address.* FROM address WHERE address.user_id=` + user_id
    } else {
        var sql_str = `SELECT * FROM address WHERE id='` + id + `'`
    }
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//删除地址
router.post('/address_delete', async ctx => {
    var id = ctx.request.body.id
    var sql_str = `DELETE FROM address WHERE id=` + id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '失败' })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})

//商品表接口

//商品读取
router.post('/goods_read', async ctx => {
    var id = ctx.request.body.id
    if (id == undefined) {
        var sql_str = `SELECT * FROM goods`
    } else {
        var sql_str = `SELECT * FROM goods where id=` + id
    }
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {

                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//商品添加
router.post('/goods_add', async ctx => {
    var data = ctx.request.body.data
    //图片保存服务器
    var img = ctx.request.files.file
    var du = fs.createReadStream(img.path)
    var my_time = new Date()
    var my_time = my_time.getTime()
    var suijishu = parseInt((Math.random()) * 100000)
    my_time = (my_time - 0) + suijishu
    var xie = fs.createWriteStream("./static/img/" + my_time + ".jpg")
    du.pipe(xie)
    data = JSON.parse(data)
    data.imgs.push({ name: my_time + ".jpg", url: 'http://47.117.121.44:3000/img/' + my_time + ".jpg" })
    var sql_str = `
            INSERT INTO goods(name,price,classify,text,list,select_num,imgs)
            VALUES ('`+ data.name + `',
            '`+ data.price + `',
            '`+ data.classify + `',
            '`+ data.text + `',
            '`+ JSON.stringify(data.list) + `',
            '`+ data.select_num + `',
            '`+ JSON.stringify(data.imgs) + `')`
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//商品删除
router.post('/goods_delete', async ctx => {
    var id = ctx.request.body.id
    var sql_str = `DELETE FROM goods WHERE id=` + id
    var sql_str1 = `select * from goods where id=` + id
    db.query(sql_str1, function (err, data, zi_Duan) {
        if (data != "") {
            data = JSON.stringify(data)
            data = JSON.parse(data)
            data = JSON.parse(data[0].imgs)
            if (data != '[]') {
                for (let i = 0; i < data.length; i++) {
                    fs.unlink("./static/img/" + data[i].name, function (err) {
                        if (err) {
                            console.log(err)
                        }
                    })
                }
            }
        }
    })
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//商品图片删除
router.post('/goods_img_delete', async ctx => {
    var file = ctx.request.body.file
    var id = ctx.request.body.id
    var sql_str = `select imgs from goods where id=` + id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                let imgs = JSON.parse(JSON.parse(data)[0].imgs)
                for (let i = 0; i < imgs.length; i++) {
                    if (imgs[i].name == file.name) {
                        imgs.splice(i, 1)
                        fs.unlink("./static/img/" + file.name, function (err) {
                            console.log("删除了" + file.name)
                            if (err) {
                                console.log(err)
                            }
                        })
                    }
                }
                var sql_str1 = `
                    UPDATE goods SET 
                    imgs='`+ JSON.stringify(imgs) + `' 
                    WHERE id=`+ id
                db.query(sql_str1, function (err1, data1, zi_duan) {
                    if (err1) {
                        f2({ code: 1, msg: '读取失败', err: err1 })
                    } else {
                        f1({ code: 0, msg: '成功', data: data1 })
                    }
                })
            }
        })
    })
    ctx.body = await promise
})
//有商品图片的数据修改
router.post('/goods_img_add', async ctx => {
    var id = ctx.request.body.id
    var img = ctx.request.files.file
    var du = fs.createReadStream(img.path)
    var my_time = new Date()
    var my_time = my_time.getTime()
    var suijishu = parseInt((Math.random()) * 100000)
    my_time = (my_time - 0) + suijishu
    var xie = fs.createWriteStream("./static/img/" + my_time + ".jpg")
    du.pipe(xie)
    var sql_str = `select * from goods where id=` + id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                data = data.replace(/\\/g, "");
                data = data.replace(/:"\[/g, ":[");
                data = data.replace(/\]"/g, "]");
                data = data.replace(/:"{/g, ":{");
                data = data.replace(/}"/g, "}");
                data = JSON.parse(data)[0]
                data.imgs.push({ name: my_time + ".jpg", url: 'http://47.117.121.44:3000/img/' + my_time + ".jpg" })
                var sql_str1 = `
                UPDATE goods SET 
                imgs='`+ JSON.stringify(data.imgs) + `' 
                WHERE id=`+ (data.id - 0)
                db.query(sql_str1, function (err1, data1, zi_duan) {
                    if (err1) {
                        f2({ code: 1, msg: '读取失败', err: err1 })
                    } else {
                        f1({ code: 0, msg: '成功', data: data1 })
                    }
                })
            }
        })
    })
    ctx.body = await promise
})

//商品数据修改
router.post('/goods_change', async ctx => {
    var data_goods = ctx.request.body.data_goods
    var sql_str = `
    UPDATE goods SET 
    name='`+ data_goods.name + `',
    price='`+ data_goods.price + `',
    classify='`+ data_goods.classify + `',
    text='`+ data_goods.text + `',
    list='`+ JSON.stringify(data_goods.list) + `',
    imgs='`+ JSON.stringify(data_goods.imgs) + `',
    select_num=`+ data_goods.select_num + `
    WHERE id=`+ data_goods.id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})

//购物车表接口

//购物车添加
router.post('/cart_add', async ctx => {
    var data_cart = ctx.request.body.data_cart
    var sql_str = `
        INSERT INTO carts(user_id,goods_id,select_num)
        VALUES ('`+ data_cart.user_id + `',
        '`+ data_cart.goods_id + `',
        '`+ data_cart.select_num + `')`
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//购物车读取
router.post('/cart_read', async ctx => {
    var id = ctx.request.body.id
    var sql_str = `SELECT goods.imgs,goods.list,goods.name,goods.price,goods.text,carts.* FROM carts,goods WHERE carts.goods_id=goods.id AND carts.user_id=` + id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//购物车删除
router.post('/cart_delete', async ctx => {
    var id = ctx.request.body.id
    var sql_str = ` DELETE FROM carts WHERE id=` + id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//购物车修改
router.post('/cart_change', async ctx => {
    var data_cart = ctx.request.body.data_cart
    var sql_str = `
    UPDATE carts 
    SET select_num=`+ data_cart.select_num + `
    WHERE id=`+ data_cart.id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})

//订单表接口

//订单添加
router.post('/order_add', async ctx => {
    var data_order = ctx.request.body.data_order
    var sql_str = `
    INSERT INTO orders
    (user_id,goods_id,address_id,select_num,is_payment,is_deliver,is_get,estimate,is_refund,is_to_refund) 
    VALUES 
    (`+ data_order.user_id + `,
    `+ data_order.goods_id + `,
    `+ data_order.address_id + `,
    `+ data_order.select_num + `,
    '`+ data_order.is_payment + `',
    '`+ data_order.is_deliver + `',
    '`+ data_order.is_get + `',
    '`+ data_order.estimate + `',
    '`+ data_order.is_refund + `',
    '`+ data_order.is_to_refund + `')`
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//订单读取
router.post('/order_read', async ctx => {
    var id = ctx.request.body.id
    var user_id = ctx.request.body.user_id
    if (id == undefined && user_id == undefined) {
        var sql_str = `
        SELECT 
        orders.id,
        users.name AS user_name,
        users.phone AS user_phone,
        goods.name AS goods_name,
        goods.price,
        address.name AS get_name,
        address.tel AS get_phone,
        address.province,
        address.city,
        address.county,
        address.addressDetail,
        address.postalCode,
        orders.estimate,
        orders.is_payment,
        orders.is_deliver,
        orders.is_get,
        orders.is_refund,
        orders.is_to_refund,
        orders.select_num 
        FROM orders,users,goods,address 
        WHERE orders.id IN (SELECT id FROM orders) 
        and orders.user_id=users.id
        and orders.goods_id=goods.id 
        AND orders.address_id=address.id`
    }
    else if (id == undefined) {
        var sql_str = `SELECT goods.imgs,goods.list,goods.name,goods.price,goods.text,orders.* FROM orders,goods WHERE orders.goods_id=goods.id and orders.user_id=` + user_id
    } else {
        var sql_str = `SELECT goods.imgs,goods.list,goods.name,goods.price,goods.text,orders.* FROM orders,goods WHERE orders.goods_id=goods.id and orders.id=` + id
    }
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//订单修改
router.post('/order_change', async ctx => {
    var data_order = ctx.request.body.data_order
    var sql_str = `
    UPDATE orders 
    SET 
    select_num=`+ data_order.select_num + `,
    is_payment='`+ data_order.is_payment + `',
    is_deliver='`+ data_order.is_deliver + `',
    is_get='`+ data_order.is_get + `',
    estimate='`+ data_order.estimate + `',
    is_refund='`+ data_order.is_refund + `',
    is_to_refund='`+ data_order.is_to_refund + `'  
    WHERE id=`+ data_order.id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//订单删除
router.post('/order_delete', async ctx => {
    var id = ctx.request.body.id
    var sql_str = `DELETE FROM orders WHERE id=` + id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})

//分类表接口
//分类读取
router.post('/classifys_read', async ctx => {
    var sql_str = `SELECT * from classifys`
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//分类修改/保存
router.post('/classifys_keep', async ctx => {
    var data_classify = ctx.request.body.data_classify
    data_classify.ico = JSON.stringify(data_classify.ico)
    data_classify.text1 = JSON.stringify(data_classify.text1)
    data_classify.text2 = JSON.stringify(data_classify.text2)
    var sql_str = `
    UPDATE classifys SET 
    name='`+ data_classify.name + `',
    ico='`+ data_classify.ico + `',
    text1='`+ data_classify.text1 + `',
    text2='`+ data_classify.text2 + `'
    WHERE id=`+ data_classify.id
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
//分类添加
router.post('/classifys_add', async ctx => {
    var data_classify = ctx.request.body.data_classify
    data_classify.ico = JSON.stringify(data_classify.ico)
    data_classify.text1 = JSON.stringify(data_classify.text1)
    data_classify.text2 = JSON.stringify(data_classify.text2)
    var sql_str = `
    INSERT INTO classifys
    (name,ico,text1,text2) 
    VALUES('`+ data_classify.name + `',
    '`+ data_classify.ico + `',
    '`+ data_classify.text1 + `',
    '`+ data_classify.text2 + `')`
    var promise = new Promise(function (f1, f2) {
        db.query(sql_str, function (err, data, zi_duan) {
            if (err) {
                f2({ code: 1, msg: '读取失败', err })
            } else {
                data = JSON.stringify(data)
                f1({ code: 0, msg: '成功', data })
            }
        })
    })
    ctx.body = await promise
})
module.exports = router


//商品修改/保存
// router.post('/goods_keep', async ctx => {
//     var data_goods = ctx.request.body.data_goods
//     data_goods.imgs = JSON.stringify(data_goods.imgs)
//     data_goods.list = JSON.stringify(data_goods.list)
    // var sql_str = `
    // UPDATE goods SET 
    // name='`+ data_goods.name + `',
    // price='`+ data_goods.price + `',
    // classify='`+ data_goods.classify + `',
    // text='`+ data_goods.text + `',
    // list='`+ data_goods.list + `',
    // select_num=`+ data_goods.select_num + `,
    // imgs='`+ data_goods.imgs + `' 
    // WHERE id=`+ data_goods.id
//     var promise = new Promise(function (f1, f2) {
//         db.query(sql_str, function (err, data, zi_duan) {
//             if (err) {
//                 f2({ code: 1, msg: '读取失败', err })
//             } else {
//                 data = JSON.stringify(data)
//                 f1({ code: 0, msg: '成功', data })
//             }
//         })
//     })
//     ctx.body = await promise
// })